﻿Imports System.Data
Imports System.Data.SqlClient

Imports BE = BusinessEntities

Public Class RequisicionDAO

    Dim conexion As SqlConnection
    Dim da As SqlDataAdapter
    Dim str_error As String
    Dim bool_resultado As Boolean
    Dim cmd As SqlCommand

    Dim cn As New Conexion

    Public Function DATA_REQUESICION(ByVal objeto As BE.ProyectoDTO) As DataSet
        Dim ds As New DataSet
        Try
            conexion = cn.Conectar
            da = New SqlDataAdapter("SP_CARGA_DATA_REQUESICION", conexion)
            da.SelectCommand.CommandType = CommandType.StoredProcedure

            da.SelectCommand.Parameters.Add(New SqlParameter("@COD_PRY", SqlDbType.Int))
            da.SelectCommand.Parameters("@COD_PRY").Direction = ParameterDirection.Input
            da.SelectCommand.Parameters("@COD_PRY").Value = objeto.CodigoProyecto

            da.Fill(ds, "DATA_REQUISICION")

        Catch ex As Exception
            str_error = ex.Message
        Finally
            conexion.Dispose()
            ds.Dispose()
            da.Dispose()
        End Try

        Return ds

    End Function
    Public Function ListarGastos() As DataSet
        Dim ds As New DataSet
        Try
            conexion = cn.Conectar
            da = New SqlDataAdapter("SP_LISTARGASTOS", conexion)
            da.SelectCommand.CommandType = CommandType.StoredProcedure

            da.Fill(ds, "listarGastos")

        Catch ex As Exception
            str_error = ex.Message
        Finally
            conexion.Dispose()
            ds.Dispose()
            da.Dispose()
        End Try

        Return ds

    End Function
    Public Function listarDetallesAdicionales(ByVal objeto As BE.RequisicionDTO) As DataSet
        Dim ds As New DataSet
        Try
            conexion = cn.Conectar
            da = New SqlDataAdapter("SP_DETALLE_ADICIONALES", conexion)
            da.SelectCommand.CommandType = CommandType.StoredProcedure

            da.SelectCommand.Parameters.Add(New SqlParameter("@NRO_REQ", SqlDbType.Int))
            da.SelectCommand.Parameters("@NRO_REQ").Direction = ParameterDirection.Input
            da.SelectCommand.Parameters("@NRO_REQ").Value = objeto.Numero_Requisicion

            da.Fill(ds, "DATA_ADICIONALES_DETALLES")

        Catch ex As Exception
            str_error = ex.Message
        Finally
            conexion.Dispose()
            ds.Dispose()
            da.Dispose()
        End Try

        Return ds

    End Function



    Public Function ListarServicios() As DataSet
        Dim ds As New DataSet
        Try
            conexion = cn.Conectar
            da = New SqlDataAdapter("SP_LISTARSERVICIOS", conexion)
            da.SelectCommand.CommandType = CommandType.StoredProcedure
            da.Fill(ds, "listarServicios")
        Catch ex As Exception
            str_error = ex.Message
        Finally
            conexion.Dispose()
            ds.Dispose()
            da.Dispose()
        End Try

        Return ds

    End Function

    Public Function ListarTecnologia() As DataSet
        Dim ds As New DataSet
        Try
            conexion = cn.Conectar
            da = New SqlDataAdapter("SP_LISTARTECNOLOGIA", conexion)
            da.SelectCommand.CommandType = CommandType.StoredProcedure
            da.Fill(ds, "listarTecnologia")
        Catch ex As Exception
            str_error = ex.Message
        Finally
            conexion.Dispose()
            ds.Dispose()
            da.Dispose()
        End Try

        Return ds

    End Function


    Public Function recuperarMaximoRequirimiento() As Int32
        conexion = cn.Conectar

        Dim comando As New SqlCommand("SP_SACAR_MAXIMO_REQ", conexion)
        comando.CommandType = CommandType.StoredProcedure

        comando.Parameters.Add(New SqlParameter("@nro_req", SqlDbType.Int))
        comando.Parameters("@nro_req").Direction = ParameterDirection.Output

        conexion.Open()
        comando.ExecuteNonQuery()
        conexion.Close()

        Dim numeromov As String
        numeromov = comando.Parameters(0).Value
        Return numeromov
        comando.Dispose()
        conexion.Dispose()
    End Function

    Public Function listarRequisicion(ByVal objeto As BE.RequisicionDTO) As DataSet
        Dim ds As New DataSet
        Try
            conexion = cn.Conectar
            da = New SqlDataAdapter("SP_TOT_CST_REQUISICION", conexion)
            da.SelectCommand.CommandType = CommandType.StoredProcedure

            da.SelectCommand.Parameters.Add(New SqlParameter("@NRO_REQ", SqlDbType.Int))

            da.SelectCommand.Parameters("@NRO_REQ").Direction = ParameterDirection.Input
            da.SelectCommand.Parameters("@NRO_REQ").Value = objeto.Numero_Requisicion

            da.Fill(ds, "DATA_REQUISICION")

        Catch ex As Exception
            str_error = ex.Message
        Finally
            conexion.Dispose()
            ds.Dispose()
            da.Dispose()
        End Try

        Return ds

    End Function

    Public Function InsertarRequisicion(ByVal objeto As BE.RequisicionDTO) As Boolean

        Dim cn As New Conexion
        Try
            conexion = cn.Conectar

            cmd = New SqlCommand("SP_INSERT_REQUISICION", conexion)

            cmd.CommandType = CommandType.StoredProcedure



            cmd.Parameters.Add(New SqlParameter("@NRO_ORD_VEN", SqlDbType.Int))
            cmd.Parameters("@NRO_ORD_VEN").Direction = ParameterDirection.Input
            cmd.Parameters("@NRO_ORD_VEN").Value = objeto.Numero_Orden_Venta

            cmd.Parameters.Add(New SqlParameter("@COD_EST_DOC", SqlDbType.Int))
            cmd.Parameters("@COD_EST_DOC").Direction = ParameterDirection.Input
            cmd.Parameters("@COD_EST_DOC").Value = objeto.Codigo_Estado_Documento

            cmd.Parameters.Add(New SqlParameter("@FEC_GEN_REQ", SqlDbType.DateTime))
            cmd.Parameters("@FEC_GEN_REQ").Direction = ParameterDirection.Input
            cmd.Parameters("@FEC_GEN_REQ").Value = objeto.Fecha_Generada_Requision

            cmd.Parameters.Add(New SqlParameter("@TOT_PER", SqlDbType.Int))
            cmd.Parameters("@TOT_PER").Direction = ParameterDirection.Input
            cmd.Parameters("@TOT_PER").Value = objeto.Total_Personas

            cmd.Parameters.Add(New SqlParameter("@TOT_DIA", SqlDbType.Int))
            cmd.Parameters("@TOT_DIA").Direction = ParameterDirection.Input
            cmd.Parameters("@TOT_DIA").Value = objeto.Total_Dias

            cmd.Parameters.Add(New SqlParameter("@TOT_CST", SqlDbType.Decimal))
            cmd.Parameters("@TOT_CST").Direction = ParameterDirection.Input
            cmd.Parameters("@TOT_CST").Value = objeto.Total_Costo

            cmd.Parameters.Add(New SqlParameter("@COD_SER", SqlDbType.Int))
            cmd.Parameters("@COD_SER").Direction = ParameterDirection.Input
            cmd.Parameters("@COD_SER").Value = objeto.Codigo_Servicio

            cmd.Parameters.Add(New SqlParameter("@COD_TEC", SqlDbType.Int))
            cmd.Parameters("@COD_TEC").Direction = ParameterDirection.Input
            cmd.Parameters("@COD_TEC").Value = objeto.Codigo_Tecnologia

            cmd.Parameters.Add(New SqlParameter("@COD_USU", SqlDbType.Int))
            cmd.Parameters("@COD_USU").Direction = ParameterDirection.Input
            cmd.Parameters("@COD_USU").Value = objeto.Codigo_Usuario

            cmd.Parameters.Add(New SqlParameter("@FEC_INI", SqlDbType.DateTime))
            cmd.Parameters("@FEC_INI").Direction = ParameterDirection.Input
            cmd.Parameters("@FEC_INI").Value = objeto.Fecha_Inicio

            cmd.Parameters.Add(New SqlParameter("@FEC_FIN", SqlDbType.DateTime))
            cmd.Parameters("@FEC_FIN").Direction = ParameterDirection.Input
            cmd.Parameters("@FEC_FIN").Value = objeto.Fecha_Fin


            conexion.Open()

            cmd.ExecuteNonQuery()
            bool_resultado = True

        Catch ex As Exception
            bool_resultado = False
            str_error = ex.Message
        Finally

            If conexion.State = ConnectionState.Open Then conexion.Close()
            conexion.Dispose()
            cmd.Dispose()
            cn = Nothing

        End Try

        Return bool_resultado

    End Function

    
    Public Function actualizarRequisicion(ByVal objeto As BE.RequisicionDTO) As Boolean

        Dim cn As New Conexion
        Try
            conexion = cn.Conectar

            cmd = New SqlCommand("SP_ACTUALIZAR_REQUISICION", conexion)

            cmd.CommandType = CommandType.StoredProcedure



            cmd.Parameters.Add(New SqlParameter("@NRO_REQ", SqlDbType.Int))
            cmd.Parameters("@NRO_REQ").Direction = ParameterDirection.Input
            cmd.Parameters("@NRO_REQ").Value = objeto.Numero_Requisicion

            cmd.Parameters.Add(New SqlParameter("@TOT_PER", SqlDbType.Int))
            cmd.Parameters("@TOT_PER").Direction = ParameterDirection.Input
            cmd.Parameters("@TOT_PER").Value = objeto.Total_Personas

            cmd.Parameters.Add(New SqlParameter("@TOT_DIA", SqlDbType.Int))
            cmd.Parameters("@TOT_DIA").Direction = ParameterDirection.Input
            cmd.Parameters("@TOT_DIA").Value = objeto.Total_Dias

            cmd.Parameters.Add(New SqlParameter("@TOT_CST", SqlDbType.Decimal))
            cmd.Parameters("@TOT_CST").Direction = ParameterDirection.Input
            cmd.Parameters("@TOT_CST").Value = objeto.Total_Costo


            conexion.Open()

            cmd.ExecuteNonQuery()
            bool_resultado = True

        Catch ex As Exception
            bool_resultado = False
            str_error = ex.Message
        Finally

            If conexion.State = ConnectionState.Open Then conexion.Close()
            conexion.Dispose()
            cmd.Dispose()
            cn = Nothing

        End Try

        Return bool_resultado

    End Function
    Public Function listarMontoReq(ByVal objeto As BE.RequisicionDTO) As DataSet
        Dim ds As New DataSet
        Try
            conexion = cn.Conectar
            da = New SqlDataAdapter("SP_LISTAR_MONTO_REQ", conexion)
            da.SelectCommand.CommandType = CommandType.StoredProcedure

            da.SelectCommand.Parameters.Add(New SqlParameter("@COD_USU", SqlDbType.Int))

            da.SelectCommand.Parameters("@COD_USU").Direction = ParameterDirection.Input
            da.SelectCommand.Parameters("@COD_USU").Value = objeto.Codigo_Usuario

            da.Fill(ds, "DATA_REQUISICION")

        Catch ex As Exception
            str_error = ex.Message
        Finally
            conexion.Dispose()
            ds.Dispose()
            da.Dispose()
        End Try

        Return ds

    End Function

    Public Function listarCargos() As DataSet
        Dim ds As New DataSet
        Try
            conexion = cn.Conectar
            da = New SqlDataAdapter("SP_LISTAR_CARGOS", conexion)
            da.SelectCommand.CommandType = CommandType.StoredProcedure

            da.Fill(ds, "listarCargos")

        Catch ex As Exception
            str_error = ex.Message
        Finally
            conexion.Dispose()
            ds.Dispose()
            da.Dispose()
        End Try

        Return ds
    End Function


    Public Function CapturaError() As String
        Return str_error
    End Function

End Class
